const mysql = require('mysql2/promise');

async function addMissingCities () {
  const connection = await mysql.createConnection({
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    timezone: '+08:00'
  });

  try {
    console.log('🔧 添加缺失的城市数据...\n');

    // 根据WebSocket数据定义的缺失城市
    const missingCities = [
      // 吴国缺失的城市
      { id: 212, name: '长沙', parentId: 43 },
      { id: 264, name: '江陵', parentId: 43 },

      // 许昌的子区域
      { id: 2, name: '新手村', parentId: 289, extension: '(1级)' },
      { id: 301, name: '陈留', parentId: 289, extension: '(5级)' },
      { id: 487, name: '九里山', parentId: 289, extension: '(20级)' },
      { id: 533, name: '芒砀山', parentId: 289, extension: '(30级)' },

      // 洛阳的子区域
      { id: 285, name: '官渡', parentId: 302, extension: '(40级)' },
      { id: 286, name: '虎牢关', parentId: 302, extension: '(50级)' },
      { id: 333, name: '乌巢', parentId: 302, extension: '(80级)' },
      { id: 337, name: '函谷关', parentId: 302, extension: '(55级)' },

      // 长安的子区域
      { id: 303, name: '华山', parentId: 304, extension: '(90级)' },
      { id: 311, name: '潼关', parentId: 304, extension: '(75级)' },
      { id: 414, name: '五丈原', parentId: 304, extension: '(65级)' },

      // 成都的子区域
      { id: 93, name: '落凤坡', parentId: 70, extension: '(30级)' },
      { id: 112, name: '广汉', parentId: 70, extension: '(40级)' },
      { id: 113, name: '峨眉山', parentId: 70, extension: '(50级)' },

      // 南中的子区域
      { id: 52, name: '云南', parentId: 111, extension: '(60级)' },
      { id: 143, name: '泸水', parentId: 111, extension: '(70级)' },
      { id: 351, name: '西洱湖', parentId: 111, extension: '(80级)' },

      // 汉中的子区域
      { id: 306, name: '子午谷', parentId: 117, extension: '(90级)' },
      { id: 307, name: '街亭', parentId: 117, extension: '(100级)' },
      { id: 308, name: '上方谷', parentId: 117, extension: '(120级)' },

      // 建业的子区域
      { id: 267, name: '广陵', parentId: 154 },

      // 长沙的子区域
      { id: 220, name: '巴陵', parentId: 212 },
      { id: 221, name: '洞庭湖', parentId: 212 },
      { id: 222, name: '湘水', parentId: 212, extension: '(130级)' },

      // 江陵的子区域
      { id: 137, name: '麦城', parentId: 264, extension: '(140级)' },
      { id: 161, name: '长坂坡', parentId: 264, extension: '(140级)' },
      { id: 163, name: '夷陵', parentId: 264 }
    ];

    console.log(`📝 准备添加 ${missingCities.length} 个缺失的城市...`);

    let added = 0;
    let skipped = 0;

    for (const city of missingCities) {
      try {
        // 检查城市是否已存在
        const [existing] = await connection.execute(
          'SELECT id, name FROM map_areas WHERE id = ?',
          [city.id]
        );

        if (existing.length > 0) {
          console.log(`  ⚠️ 城市已存在: ${city.name} (ID: ${city.id})`);
          skipped++;
          continue;
        }

        // 插入新城市
        await connection.execute(
          'INSERT INTO map_areas (id, name, parent_id, extension, createdAt, updatedAt) VALUES (?, ?, ?, ?, NOW(), NOW())',
          [city.id, city.name, city.parentId, city.extension || null]
        );

        const parentInfo = city.parentId ? ` (父级: ${city.parentId})` : '';
        const levelInfo = city.extension ? ` ${city.extension}` : '';
        console.log(`  ✅ 添加城市: ${city.name}${levelInfo} (ID: ${city.id})${parentInfo}`);
        added++;

      } catch (error) {
        console.log(`  ❌ 添加城市失败: ${city.name} (ID: ${city.id}) - ${error.message}`);
      }
    }

    console.log(`\n📊 添加结果:`);
    console.log(`  成功添加: ${added} 个城市`);
    console.log(`  跳过已存在: ${skipped} 个城市`);

    // 验证最终结果
    console.log('\n🔍 验证最终层级关系...');

    const hierarchyData = {
      100: { name: '魏国', children: [289, 302, 304] },
      19: { name: '蜀国', children: [70, 111, 117] },
      43: { name: '吴国', children: [154, 212, 264] }
    };

    for (const [areaId, areaData] of Object.entries(hierarchyData)) {
      console.log(`\n${areaData.name} (ID: ${areaId}):`);

      for (const cityId of areaData.children) {
        const [cityInfo] = await connection.execute(
          'SELECT id, name, parent_id, extension FROM map_areas WHERE id = ?',
          [cityId]
        );

        if (cityInfo.length > 0) {
          const city = cityInfo[0];
          const cityName = city.extension ? `${city.name}${city.extension}` : city.name;
          console.log(`  └─ ${cityName} (ID: ${cityId}, 父级: ${city.parent_id})`);

          // 显示子区域
          const [children] = await connection.execute(
            'SELECT id, name, parent_id, extension FROM map_areas WHERE parent_id = ? ORDER BY id',
            [cityId]
          );

          children.forEach(child => {
            const childName = child.extension ? `${child.name}${child.extension}` : child.name;
            console.log(`    └─ ${childName} (ID: ${child.id})`);
          });
        }
      }
    }

    console.log('\n🎉 缺失城市添加完成！');

  } catch (error) {
    console.error('❌ 添加失败:', error.message);
    if (error.code) {
      console.error('错误代码:', error.code);
    }
  } finally {
    await connection.end();
    console.log('\n🔌 数据库连接已关闭');
  }
}

// 运行脚本
if (require.main === module) {
  addMissingCities().catch(console.error);
}

module.exports = { addMissingCities };
